<?php
/**
 * MySQL DataBase class 05.10.2012
 * 
 * @author Mart Stukolkin <www24.ee> 
 * @copyright Copyright (c) 2011-2012, WWW24 OÜ
*/
class DataBase {
	
	private $dbConnect;
	
	public $devMode = FALSE;
	
	/**
	 * @param database
	 * @param username
	 * @param password
	 * @param server default 'localhost'
	 */
	function __construct($database, $username, $password, $server = "localhost") {
		$this->dbConnect = mysql_connect($server, $username, $password);
		mysql_select_db($database, $this->dbConnect);
	}
	
	function __destruct() {
		mysql_close($this->dbConnect);	
	}
	
	/**
	 * @param sql_act
	 * @param sql_values
	 * @param sql_params
	 * @return Returns array()
	 */
	function Query ($sql_act, $sql_values, $sql_params) {
		$queryStart = microtime(true);
		
		$sqlTABLE = empty($sql_params['table']) ? null : $sql_params['table'];
		$sqlWHERE = empty($sql_params['where']) ? null : $sql_params['where'];
		$sqlORDER = empty($sql_params['order']) ? null : $sql_params['order'];
		$sqlLIMIT = empty($sql_params['limit']) ? null : $sql_params['limit'];
		$NULL = FALSE;
						
		if ($sqlWHERE) {
			$where = "WHERE ";
			foreach ($sqlWHERE AS $key=>$val) {
				if(preg_match('/|/',$key)) { $key=explode("|",$key); $key=$key[0]; }
				if(preg_match('/>/',$val)) {$val=preg_replace('/>/',"",$val); $eq=">=";}
				elseif(preg_match('/</',$val)) {$val=preg_replace('/</',"",$val); $eq="<=";}
				elseif(preg_match('/!/',$val)) {$val=preg_replace('/!/',"",$val); $eq="!=";}
				elseif(preg_match('/%LIKE%/',$val)) {$val=preg_replace('/%LIKE%/',"",$val); $eq=" LIKE"; }
				elseif(preg_match('/IS NOT NULL/',$val)) {$val=preg_replace('//',"",$val); $eq=" "; $NULL=true;}
				elseif(preg_match('/IS NULL/',$val)) {$val=preg_replace('//',"",$val); $eq=" "; $NUL=true;}
				else { $eq="="; $NULL=false; }
				if(!isset($val)) $val='null'; else $uc="'";
				if($NULL) $uc = "";
				$where .= $key.$eq.$uc.$val.$uc." AND ";
			}
			$where = substr($where,0,-4);
			if(preg_match('/ORS=\'1\' AND/',$where)) $where=preg_replace('/ORS=\'1\' AND/',"(",$where);
			if(preg_match('/AND ORV=\'1\' AND/',$where)) $where=preg_replace('/AND ORV=\'1\' AND/'," OR ",$where);
			if(preg_match('/AND ORE=\'1\'/',$where)) $where=preg_replace('/AND ORE=\'1\'/',")",$where);
		} else { $where = ""; }
				
		if($sql_act=="INSERT") { $keys=''; $vals='';
	    	foreach($sql_values AS $key=>$val) {
				$val = preg_replace('/\'/',"\'", $val);
				$val = preg_replace('/NOW\(\)/',"'+NOW()+'", $val);
				$keys .= "{$key}, ";
				$vals .= "'{$val}', ";
	    	}
	    	$sql = "INSERT INTO ".$sqlTABLE." (".substr($keys,0,-2).") VALUES (".substr($vals,0,-2).")";
		}
		if(!empty($sql_params['time'])) $selW = "*,year(time),month(time),day(time),hour(time) ,minute(time), second(time)";
		elseif(!empty($sql_params['select'])) $selW = $sql_params['select'];
		elseif(!empty($sql_params['count'])) $selW = "COUNT(*)";
		else $selW="*";
		if($sql_act=="SELECT") $sql = "SELECT ".$selW." FROM ".$sqlTABLE." ".$where." ".$sqlORDER." ".$sqlLIMIT;
		
		if($sql_act=="UPDATE") { $set='';
			foreach($sql_values AS $key=>$val) {
				$val=preg_replace('/NOW\(\)/',"'+NOW()+'",$val);
				$set .= $key."='".$val."', ";
	    	}
			$sql = "UPDATE ".$sqlTABLE." SET ".substr($set,0,-2)." ".$where;
		}
		if($sql_act=="DELETE") $sql ="DELETE FROM ".$sqlTABLE." ".$where;
		
		if(!empty($sql_params['debug'])) print $sql;
				
		if($this->devMode){
			$query = mysql_query($sql, $this->dbConnect) or die($sql.'<br/>|'.mysql_error());
		} else {
			$query = mysql_query($sql, $this->dbConnect) or die('Database connection error:'.error_log(mysql_error()));
		}

		
		if($sql_act=="INSERT") { $query2 = mysql_query("SELECT LAST_INSERT_ID()", $this->dbConnect); $ret=mysql_fetch_assoc($query2); $ret=$ret['LAST_INSERT_ID()']; }
	
		if($sql_act=="SELECT") { $count=0; while($res = mysql_fetch_assoc($query)) { $ret[]=$res; $count++; } }
		
		$queryEnd = microtime(true);
		
		$queryTime = number_format(($queryEnd - $queryStart),5);
	
		if (!empty($sql_params['debug'])) {
			$count = 0;
			print " ($queryTime) - <font color=\"red\" size=\"4\">$count</font><BR><BR>"; 
		}
		
		if(empty($ret)) $ret=array();
		return $ret;
    }

	/**
	 * @param query
	 * @param debug = 0
	 * @return Returns array()
	 */
	function QuerySQL($sql, $debug=0){
		$queryStart = microtime(true);
	    
	    if($debug) print $sql;
			    
	    if(preg_match('/SELECT/',$sql)){
	      $query = mysql_query($sql, $this->dbConnect) or die('<br>'.mysql_error());
	      while($res = mysql_fetch_assoc($query)) {$ret[]=$res; $count++; } 
	    } else {
	      $query = mysql_query($sql, $this->dbConnect) or die('<br>'.mysql_error());
	    }
		
		$queryEnd = microtime(true);
		
		$queryTime = number_format(($queryEnd - $queryStart),5);
	    
	    if($debug) print ' ('.$queryTime.') - <font color="red" size="4">'.$count.'</font><BR><BR>';
	    
	    if(empty($ret)) $ret=array();
	    return $ret;
    }
	
	/**
	 * @param tableName
	 * @param input = array()
	 * @param debug = 0
	 * @return ID
	 */
	function setTable($table, $input=array(), $debug=0) {
		return $this->Query('INSERT', $input, array('table'=>$table, 'debug'=>$debug));
	}

	/**
	 * @param tableName
	 * @param where array() or ID
	 * @param name = ''
	 * @param debug = 0
	 * @return Returns array()
	 */
	function getTable($table, $where, $name='', $debug=0) {
		if(!is_array($where) && is_numeric($where)) $where = array('id'=>$where);
		$ret = $this->Query('SELECT', array(), array('table'=>$table, 'debug'=>$debug, 'where'=>$where ));
		$ret = !empty($ret) ? $ret[0] : null;
		if(!empty($name)) return $ret[$name]; else return $ret;
	}
	
	/**
	 * @param tableName
	 * @param where array()
	 * @param order = ''
	 * @param debug = 0
	 * @param limit = '-1'
	 * @param limit = '100'
	 * @return Returns array()
	 */
	function getTableList($table, $where=array(), $order='', $debug=0, $limit='-1', $show='100') {
		$qLimit = "";
		if($limit >= 0){
			if(!$limit){ $limit = 0; } 
			$qLimit = "OFFSET ".$limit." LIMIT ".$show;
		}
		return $this->Query('SELECT', array(), array('table'=>$table, 'debug'=>$debug, 'where'=>$where, 'order'=>$order, 'limit'=>$qLimit));
	}
	
	/**
	 * @param tableName
	 * @param where array()
	 * @param debug = 0
	 * @return Returns int
	 */
	function getTableCount($table, $where=array(), $debug=0) {
		$ret = $this->Query('SELECT', array(), array('table'=>$table, 'debug'=>$debug, 'where'=>$where, 'count'=>true));
		return $ret[0]['COUNT(*)'];
	}
	
	/**
	 * @param tableName
	 * @param where array() or ID
	 * @param input = array()
	 * @param debug = 0
	 */
	function setTableUpdateDB($table, $where, $input=array(), $debug=0) {
		if(!is_array($where) && is_numeric($where)) $where = array('id'=>$where);
		return $this->Query('UPDATE', $input, array('table'=>$table, 'debug'=>$debug, 'where'=>$where ));
	}
		
}